import pymysql

class DBTools(object):
    # 建立数据库连接 类方法
    @classmethod
    def __get_conn(cls):
        conn = pymysql.connect(host="211.103.136.244", port=7061, user="student",
                               password="iHRM_student_2021", database="ihrm", charset="utf8")
        return conn

    # 查询一条
    @classmethod
    def select_one(cls,sql):
        conn = None
        cursor = None
        res = None
        try:
            # 调用自己封装的建立连接的方法，得到一个conn
            conn = cls.__get_conn()
            # 获取游标
            cursor = conn.cursor()
            # 执行SQL
            cursor.execute(sql)
            # 提取结果集数据
            res = cursor.fetchone()
        except Exception as e:
            print(e)
        finally:
            # 关闭游标和连接
            cursor.close()
            conn.close()
            return res


    # 查询多条
    @classmethod
    def select_many(cls):
        pass

    # 增删改(update,insert,delete)
    @classmethod
    def uid_db(cls,sql):
        conn = None
        cursor = None
        try:
            # 调用自己封装的建立连接的方法，得到一个conn
            conn = cls.__get_conn()
            # 获取游标
            cursor = conn.cursor()
            # 执行SQL
            cursor.execute(sql)
            # 事务提交
            conn.commit()
            print("事务提交成功")
            print("影响行数:",conn.affected_rows())
        except Exception as e:
            print(e)
            # 事务回滚
            conn.rollback()
            print("事务回滚")
        finally:
            # 关闭游标和连接
            cursor.close()
            conn.close()


if __name__ == '__main__':
    # 类名.方法名
    # res = DBTools.select_one("select * from t_book where id=1")
    # print(res)
    DBTools.uid_db("DELETE FROM bs_user WHERE mobile = '13810229003';")